﻿--获取订单(分配维护客服时用)
CREATE PROCEDURE [dbo].[proc_Order_Getlist_AllotService]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@cusflag int --1按客服名查，0按客户名查
	)
AS
Begin
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

Declare @cSql varchar(2000)

Set @cSql = 'CompanyId ='+Convert(varchar,@CompanyId)+' And NOE_Flag=0 and [Order].CreateDate>=@sRq And [Order].CreateDate<=@eRq '

If @StateId>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)
Else
	Set @cSql = @cSql + ' And StateId in (3,4,5) '
	
If @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

If @WayId<>0
	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

If @ProcessId<>0
	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

If @Stext<>''
	if @cusflag=0
		Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' )'
	else
		Set @cSql = @cSql + ' And (TrackKfName like ''%'+@Stext+'%'')'

If @DepId<>0
	Set @cSql = @cSql +' and DepId in(Select Id From department Where Left(bmbh,Len('''+@bmbh_T+'''))='''+@bmbh_T+''')'
-- (@DepId=0 Or DepId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))

Set @cSql='

Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,'''+@StartDate+' 00:00:00'')
	Set @eRq = Convert(Datetime,'''+@EndDate+' 23:59:59'')
	
;WITH list As(Select Top 2000 ROW_NUMBER() OVER (ORDER BY TrackKfName,[Order].CreateDate DESC)AS Row
		,[Order].Id
		,OId
		,isnull(OrderId,0) as OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,[Order].TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,[Order].CreateDate
		,isnull(YingShouPrice,0) as YingShouPrice
		,isnull(ShiShouPrice,0) as ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		--,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,[Order].CompanyId
		,ExCompanyId
		,[Order].StateId
		,IsGreen
		,TrackKfName
		,IsNull(TrackKfAccount,''0000'') As TrackKfAccount
		,ISNULL(TrackKfDepId,0) As TrackKfDepId
		,ISNULL(FinanceRenlingDate,''1900-01-01'') As FinanceRenlingDate
		,NOE_Flag
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		From [Order]
		Where   '+@cSql+'
	)

	Select *,IsNULL((Select Title From Order_State Where Id=list.StateId),''...'') As StateName,
	IsNull((Select Top 1 [State] From Employee Where UserName=TrackKfAccount),0) As BankId,	--人员在职状态
	(Select Count(0) From list) As RecordCount From list
	Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' Order By Row'

	Exec (@cSql)
End
